using System.Data.SqlClient;
using System;
using System.Data;
namespace Gl.SettingsManager.Providers.DbSettings
{
	internal class DbHelper
	{
		public static bool SettingsDataBaseExists(string connection)
		{
			bool exists = false;

			ExecuteCommandDelegate deleg = delegate(SqlCommand cmd, object[] objs)
			{
				cmd.CommandText = DbConstants.CheckTablesExistense;
				using (SqlDataReader reader = cmd.ExecuteReader())
				{
					reader.Read();
					exists = !(reader["SectionsTable"] == DBNull.Value ||
						reader["SettingsTable"] == DBNull.Value);
				}
			};

			Utils.ExecuteCommand(connection, false, deleg);

			return exists;
		}

		public static void ResetSettings(string connection)
		{
			ExecuteCommandDelegate deleg = delegate(SqlCommand cmd, object[] objs)
			{
				cmd.CommandText = string.Format(@"DROP TABLE {0}
									DROP TALBE {1}", DbConstants.Settings.TableName, DbConstants.Sections.TableName);
				cmd.ExecuteNonQuery();
			};

			Utils.ExecuteCommand(connection, false, deleg);
		}

		public static void CreateTable(string connection)
		{
			ExecuteCommandDelegate deleg = delegate(SqlCommand cmd, object[] objs){
				cmd.CommandText = Sql.CreateDbSql;
				cmd.CommandType = CommandType.Text;
				cmd.ExecuteNonQuery();
			};

			Utils.ExecuteCommand(connection, false, deleg);
		}

		public static SettingsCollection GetSettingsCollection(string appName, 
			string context, string typeName, string connection)
		{
			SettingsCollection collection = new SettingsCollection(typeName, context);
			ExecuteCommandDelegate deleg = delegate(SqlCommand cmd, object[] objs)
			{
				string command = @"SELECT Settings.{0} Name, Settings.{1} as Value
					FROM {2} Settings LEFT JOIN {3} Sections 
					ON Sections.{4} = Settings.{5}
					WHERE 
					Sections.{6} = @TypeName AND
					Sections.{7} = @Context AND
					Sections.{8} = @ApplicationName";
				command = string.Format(command, DbConstants.Settings.NameColumn, DbConstants.Settings.ValueColumn,
					DbConstants.Settings.TableName, DbConstants.Sections.TableName, DbConstants.Sections.IdColumn,
					DbConstants.Settings.SectionIdColumn, DbConstants.Sections.TypeNameColumn, DbConstants.Sections.ContextColumn,
					DbConstants.Sections.ApplicationNameColumn);

				cmd.CommandText = command;

				cmd.Parameters.Add(new SqlParameter("@TypeName", SqlDbType.NVarChar)).Value = typeName;
				cmd.Parameters.Add(new SqlParameter("@Context", SqlDbType.NVarChar)).Value = context;
				cmd.Parameters.Add(new SqlParameter("@ApplicationName", SqlDbType.NVarChar)).Value = appName;

				using (SqlDataReader dr = cmd.ExecuteReader())
				{
					while (dr.Read())
					{
						string value = dr["Value"] != DBNull.Value ? (string)dr["Value"] : string.Empty;
						collection.Add((string)dr["Name"], value);
					}
				}
			};
			Utils.ExecuteCommand(connection, false, deleg, false);
			return collection;
		}

		public static void SaveSettingsCollection(SettingsCollection settings, string appName, string connection,
			bool useTrasaction)
		{
			ExecuteCommandDelegate deleg = delegate(SqlCommand cmd, object[] objs)
			{
				cmd.CommandText = string.Format(@"
									DELETE FROM {4} WHERE {5} = (SELECT {6} FROM {0}
									WHERE 
									{1} = @Context AND 
									{2} = @TypeName AND
									{3} = @ApplicationName
									)
									DELETE FROM {0} WHERE 
									{1} = @Context AND 
									{2} = @TypeName AND
									{3} = @ApplicationName",
					DbConstants.Sections.TableName,
					DbConstants.Sections.ContextColumn,
					DbConstants.Sections.TypeNameColumn,
					DbConstants.Sections.ApplicationNameColumn,
					DbConstants.Settings.TableName,
					DbConstants.Settings.SectionIdColumn,
					DbConstants.Sections.IdColumn);

				cmd.Parameters.Add(new SqlParameter("@Context", SqlDbType.NVarChar)).Value = settings.ContextName;
				cmd.Parameters.Add(new SqlParameter("@TypeName", SqlDbType.NVarChar)).Value = settings.TypeName;
				cmd.Parameters.Add(new SqlParameter("@ApplicationName", SqlDbType.NVarChar)).Value = appName;

				cmd.ExecuteNonQuery();

				cmd.CommandText = string.Format(@"INSERT INTO {0}({1}, {2}, {3})  VALUES(@Context, @TypeName, @ApplicationName) SELECT @@IDENTITY",
					DbConstants.Sections.TableName,
					DbConstants.Sections.ContextColumn,
					DbConstants.Sections.TypeNameColumn,
					DbConstants.Sections.ApplicationNameColumn);

				cmd.Parameters["@Context"].Value = settings.ContextName;
				cmd.Parameters["@TypeName"].Value = settings.TypeName;
				cmd.Parameters["@ApplicationName"].Value = appName;

				int sectionId = Convert.ToInt32(cmd.ExecuteScalar());

				cmd.Parameters.Clear();
				cmd.CommandText = string.Format("INSERT INTO {0}({1}, {2}, {3}) VALUES(@SectionId, @Name, @Value)",
					DbConstants.Settings.TableName, 
					DbConstants.Settings.SectionIdColumn, 
					DbConstants.Settings.NameColumn,
					DbConstants.Settings.ValueColumn);

				cmd.Parameters.Add(new SqlParameter("@SectionId", SqlDbType.Int));
				cmd.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar));
				cmd.Parameters.Add(new SqlParameter("@Value", SqlDbType.NVarChar));

				foreach (Setting setting in settings)
				{
					cmd.Parameters["@SectionId"].Value = sectionId;
					cmd.Parameters["@Name"].Value = setting.Name;
					cmd.Parameters["@Value"].Value = setting.Value;
					cmd.ExecuteNonQuery();
				}
			};

			Utils.ExecuteCommand(connection, useTrasaction, deleg);
		}
	}
}
